"""

.. module:: database
.. moduleauthor:: @author: 

This module contains all DataStore related classes and definitions.
   
.. seealso:: modules :mod:`<module>`

*Constants*:
.. data:: constant_1
        
    constant_1 is used for ......
        
.. data:: constant_2
        
    constant_2 is used for ......
"""

# Imports
#
import logging
import os
import sqlite3
import types

from datastore.resource import Resource
from datastore.uri import Uri
from runtime.boot import LoggingLevels

# Constants
#
RESOURCE_ID = 'RESOURCE_ID'
RESOURCE_OBJECT = 'RESOURCE_OBJECT'
RESOURCEMETADATA_KEY = 'RESOURCEMETADATA_KEY'
RESOURCEMETADATA_VALUE = 'RESOURCEMETADATA_VALUE'

# Classes
#

class DataStore(object):
    """The purpose of this class is to manage the DataStore - a database repository
    for Resource objects. Resources are serialised before being written to the DataStore,
    and de-serialised after being read from the DataStore. 

    *Usage examples*::

        example code

    *Common Arguments for Class methods*:
        <arguments>
    
    *Audience*:
        <audience>
    
    *Constants*:
        .. data:: constant_1
        
            constant_1 is used for ......
        
        .. data:: constant_2
        
            constant_2 is used for ......
    """
    
    # Class constants
    #
    
    # TODO: We must use a single connection to the database for all SQL, for the db lock to work successfully.
    #       Each request should allocate a new cursor, and close it after the request followed by a conn.commit()
    #
    
    def __init__(self, datastore_pathname, namespace=None):
        # Check if the db has been created. Each time an instance of DataStore
        # is created, it will access the same database file
        #
        # Parameters:
        #     <param 1> :
        #     ...
        #
        
        self.logger = logging.getLogger('yarra.datastore.database.DataStore')
        self.__datastore_pathname = datastore_pathname
        self.__ns = namespace
        
        if (not os.path.isfile(self.__datastore_pathname)):
            self._create_database()
    
    def lock(self):
        """This function locks the datastore from other processes. The only way 
        we can lock the SQLite3 database is by performing the following actions:
        
        1. SQL query "PRAGMA locking_mode=EXCLUSIVE"
        2. SQL INSERT or UPDATE.
        3. Perform all SQL on the same connection, as the file lock is attached
           to the connection. If the connection is closed then the lock is released.
        
        The database will lock only on the first write after the PRAGMA call. 
        """
        
        conn = self._get_connection()
        cursor = conn.cursor()
        cursor.execute("""PRAGMA locking_mode=EXCLUSIVE""")
        cursor.close()
        
        key_uri = Uri(Resource.KEY_URI_DATASTORE_ROOT_NODELOCK, Resource.NS_DATASTORE)
        rs_list = None
        
        try:
            rs_list = self.find([(key_uri, "locked")])
        except NotFoundException:
            pass
        
        if (rs_list is not None) and (len(rs_list) > 0):
            rs = rs_list[0]
        else:
            rs = Resource()
            rs.set_metadata(key_uri, value="locked")
            rs.content = "Node Lock"
        
        self.put([rs], conn)
        conn.commit()

    def gets(self, ids):
        """This function returns a list of Resources that each have an id that
        exists in the argument 'ids' list.
        
        :param ids: int or List of ints - Resource id(s).
        :rtype: List - list of Resources.
        """
        
        # allow a single resource to be fetched
        #
        if (type(ids) == types.IntType) or \
           (type(ids) == types.StringType) or \
           (type(ids) == types.UnicodeType):
            ids = [ids]
        
        self.logger.log(LoggingLevels.TRACE, "ids (%s)" % ids)
        
        conn = self._get_connection()
        cursor = conn.cursor()
        
        # TODO: could not get the IN clause working, so for now it is running a separate query for each id
        #
        
        # find each resource by id
        #
        resources = []
        for id in ids:
            id = str(id)

            cursor.execute("""
                            SELECT r.id as {0}, r.object as {1}
                            FROM RESOURCE r
                            WHERE r.id = (?)
                            """.format(RESOURCE_ID, RESOURCE_OBJECT),
                            [id])
            
            # only one row should return
            #
            resource_row = cursor.fetchone()
            if (resource_row is None):
                raise NotFoundException()
            else:
                # if the resource is found, then find the metadata
                #
                cursor.execute("""
                                SELECT rmd.key as {0}, rmd.value as {1}
                                FROM RESOURCEMETADATA rmd
                                WHERE rmd.resource_id = (?)
                                """.format(RESOURCEMETADATA_KEY, RESOURCEMETADATA_VALUE),
                                [id])
                
                # there can be more than one metadata for a resource
                #
                metadata_rows = cursor.fetchall()
                
                # TODO: What do we do with 'metadata_rows'?
                #
                
                # create resource object
                #
                rs = Resource(serialised_object=resource_row[RESOURCE_OBJECT], namespaces=self.__ns)
                rs.set_id(resource_row[RESOURCE_ID])
                
                resources.append(rs)
                    
        conn.commit()
        conn.close()
        
        self.logger.log(LoggingLevels.TRACE, "resources (%s)" % resources)
        
        return resources
        
    def put(self, resources, connection=None):
        """This function either saves a new Resource or updates a existing one. 
        The argument 'resources' is a resource or a list of Resources. This
        method does not return anything.
        
        :param resources: Resource or List of Resources
        :param connection: Database connection
        """
        
        # allow a single resource to be specified
        #
        if (type(resources) == type(Resource())):
            resources = [resources]
        
        # TODO: Database file locking 
        if (connection is not None):
            conn = connection
        else:
            conn = self._get_connection()
        cursor = conn.cursor()
        
        for rs in resources:
            # If Resource does not have an 'id' then it hasn't yet been saved
            # to the database, where the 'id' is assigned. So let's INSERT it.
            #
            if (rs.id is None):
                cursor.execute("""
                                INSERT INTO RESOURCE VALUES (null, ?)
                                """,
                                [rs.serialise()])
                
                # set the id of the resource
                #
                rs.id = cursor.lastrowid
            
            # If Resource does have an 'id' then it has been saved (at some
            # time in the past) to the database. So let's UPDATE it.
            #
            else:
                cursor.execute("""
                                UPDATE RESOURCE
                                SET object = (?)
                                WHERE id = (?)
                                """,
                                [rs.serialise(), rs.id])
                
                # Delete all Metadata records for this Resource. We will create
                # new records at the next step.
                # 
                cursor.execute("""
                                DELETE FROM RESOURCEMETADATA
                                WHERE resource_id = (?)""",
                                [rs.id])
                
            # insert the resource metadata
            for [key, value] in rs.get_all_metadata():
                (key_str, value_str) = self.convert_key_value_pair_to_str((key, value))
                cursor.execute("""
                                INSERT INTO RESOURCEMETADATA VALUES (?, ?, ?)
                                """,
                                (rs.id, key_str, value_str))                
        
        cursor.close()
        
        if (connection is None):
            conn.commit()
            conn.close()
    
    def delete(self, resources, connection=None):
        """This function deletes the argument 'resources' Resources from the 
        DataStore.
        
        :param resources: Resource or list of Resources.
        :param connection: Database connection
        """
        
        # allow a single resource to be specified
        #
        if (type(resources) == type(Resource())):
            resources = [resources]
        
        if (connection is not None):
            conn = connection
        else:
            conn = self._get_connection()
        cursor = conn.cursor()

        # TODO: use single "DELETE ... WHERE id IN ..." clause rather than separate "DELETE ... WHERE id = " clauses?
        
        for rs in resources:
            # check the type first
            #
            if (not isinstance(rs, Resource)):
                raise TypeError
            
            # delete the resource. There is a trigger that will delete anything 
            # that also references this resource (i.e metadata)
            #
            cursor.execute("""
                            DELETE FROM RESOURCE
                            WHERE id = (?)
                            """,
                            [rs.id])
        
        if (connection is None):
            conn.commit()
            conn.close()
    
    def find(self, key_values=None):
        """This function returns a list of Resources that have metadata that
        match ALL those defined in the argument 'key_values'. 
        
        :param key_values: List of tuple(key, value)
        :rtype: List - list of Resources.
        """
        
        conn = self._get_connection()
        cursor = conn.cursor()
        ids = None
        
        for key_value in key_values:
            (key_str, value_str) = self.convert_key_value_pair_to_str(key_value)
            
            self.logger.log(LoggingLevels.TRACE, "key (%s), value (%s)" % (key_str, value_str))
            
            if (value_str is not None):
                cursor.execute("""
                                SELECT resource_id as {0}
                                FROM RESOURCEMETADATA
                                WHERE key = (?)
                                AND value = (?)
                                """.format(RESOURCE_ID),
                                [key_str, value_str])
            else:
                cursor.execute("""
                                SELECT resource_id as {0}
                                FROM RESOURCEMETADATA
                                WHERE key = (?)
                                """.format(RESOURCE_ID),
                                [key_str])
                
            rows = cursor.fetchall()
            if (len(rows) == 0):
                raise NotFoundException()
            else:
                if (ids is None):
                    # Store all ids found.
                    #
                    ids = [row[RESOURCE_ID] for row in rows]
                else:
                    # Store only those ids that are common to previous Metadata
                    # results and this result.
                    #
                    ids = [row[RESOURCE_ID] for row in rows if row[RESOURCE_ID] in ids]

        self.logger.log(LoggingLevels.TRACE, "ids = %s" % ids)
        
        # get the resources and return. Remove duplicate ids by coverting list to a set
        #
        return self.gets(set(ids))

    def delete_put(self, delete_resources, put_resources):
        """This function deletes the argument 'delete_resources' Resources from the
        DataStore, and adds the argument 'put_resources' Resources to the DataStore. 
        
        :param delete_resources: Resource or list of Resources to delete from DataStore.
        :param put_resources: Resource or list of Resources to add to DataStore.
        """
        
        conn = self._get_connection()
        self.delete(delete_resources, conn)
        self.put(put_resources, conn)
        conn.commit()
        conn.close()

    def delete_database(self):
        """This function deletes the current database file.
        """
        
        if (os.path.isfile(self.__datastore_pathname)):
            os.remove(self.__datastore_pathname)
    
    def _get_connection(self):
        """This function retrieves a connection to the database.
        
        :rtype: Database connection
        """
        
        conn = sqlite3.connect(self.__datastore_pathname)
        conn.row_factory = sqlite3.Row        
        return conn
        
    def _create_database(self):
        """This function creates the DataStore schema in the database.
        """
        
        conn = self._get_connection()
        cursor = conn.cursor()
            
        # Create KEY VALUE table 
        cursor.execute("""
                        CREATE TABLE RESOURCE (
                          ID INTEGER NOT NULL PRIMARY KEY,
                          OBJECT BLOB
                        )""")       

        # Create delete trigger for RESOURCE table
        cursor.execute("""
                        CREATE TRIGGER FK_RESOURCE_ID_DELETE_TRIGGER
                        BEFORE DELETE ON RESOURCE
                        FOR EACH ROW BEGIN
                            DELETE from RESOURCEMETADATA WHERE RESOURCE_ID = OLD.ID;
                        END;
                        """) 
        
        cursor.execute("""
                        CREATE TABLE RESOURCEMETADATA (
                          RESOURCE_ID INTEGER NOT NULL CONSTRAINT FK_RESOURCE_ID REFERENCES RESOURCE(ID) ON DELETE CASCADE,
                          KEY TEXT,
                          VALUE TEXT
                        )""")        

        # Create insert trigger for RESOURCEMETADATA table
        cursor.execute("""
                        CREATE TRIGGER FK_RESOURCE_ID_INSERT_TRIGGER
                        BEFORE INSERT ON RESOURCEMETADATA
                        FOR EACH ROW BEGIN
                            SELECT RAISE(ROLLBACK, 'INSERT ON TABLE "RESOURCEMETADATA" VIOLATES FOREIGN KEY CONSTRAINT "FK_RESOURCE_ID"')
                            WHERE  NEW.RESOURCE_ID IS NOT NULL
                                    AND (SELECT ID FROM RESOURCE WHERE id = NEW.RESOURCE_ID) IS NULL;
                        END;
                        """)
        
        # Create update trigger for RESOURCEMETADATA table
        cursor.execute("""
                        CREATE TRIGGER FK_RESOURCE_ID_UPDATE_TRIGGER
                        BEFORE UPDATE ON RESOURCEMETADATA
                        FOR EACH ROW BEGIN
                            SELECT RAISE(ROLLBACK, 'UPDATE ON TABLE "RESOURCEMETADATA" VIOLATES FOREIGN KEY CONSTRAINT "FK_RESOURCE_ID"')
                            WHERE  NEW.RESOURCE_ID IS NOT NULL
                                    AND (SELECT ID FROM RESOURCE WHERE id = NEW.RESOURCE_ID) IS NULL;
                        END;
                        """)         
         
        conn.commit()
        conn.close()

    def convert_key_value_pair_to_str(self, key_value):
        """This function converts the argument tuple 'key_value' into a
        key/value pair and returns.
        
        :param key_value: tuple(key, value)
        :rtype: key/value pair.
        """
        
        (key, value) = key_value
        key_str = key.full_uri()
        if (type(value) is type(Uri())):
            value_str = value.full_uri()
        elif (value is None):
            value_str = None
        else:
            value_str = str(value)
        
        return (key_str, value_str)
    
    def set_namespace(self, ns):
        """This function ... 
        
        :param param_1: <class> or <type>
        :param param_2: <class> or <type>
        :rtype: <return type>
        """
        
        self.__ns = ns
    
    def get_datastore_pathname(self):
        return self.__datastore_pathname
    
    ns = property(None, set_namespace, None, None)

class KeyValueStoreException(Exception):
    pass

class NotFoundException(Exception):
    """This class represents an exception for having not found an expected record
    in the DataStore.

    *Usage examples*::

        example code

    *Common Arguments for Class methods*:
        <arguments>
    
    *Audience*:
        <audience>
    
    *Constants*:
        .. data:: constant_1
        
            constant_1 is used for ......
        
        .. data:: constant_2
        
            constant_2 is used for ......
    """
    
    pass
